package com.bsth.repository.realcontrol;

import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.entity.schedule.CarConfigInfo;
import com.bsth.repository.BaseRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface ScheduleRealInfoRepository extends BaseRepository<ScheduleRealInfo, Long>{

	@Query("select s from ScheduleRealInfo s where s.xlBm in ?1")
	List<ScheduleRealInfo> findByLines(List<String> lines);
	
	
	@Query(value="select s from ScheduleRealInfo s where s.id = ?1 ")
	ScheduleRealInfo scheduleById(Long id);
	
	@Query(value="select s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 and bc_type not in ('in','out') order by (lpName+1),clZbh,realExecDate,dfsj")
	List<ScheduleRealInfo> scheduleDailyQp(String line, String date);
	
	@Query(value="select s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 GROUP BY s.id,s.jGh,s.clZbh,s.lpName order by (lpName+1)")
	List<ScheduleRealInfo> queryUserInfo(String line, String date);
	
	@Query(value="select min(s.id), s.jGh,s.clZbh,s.lpName,s.jName from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 GROUP BY s.jGh,s.clZbh,s.lpName ,s.jName order by (lpName+1)")
	List<ScheduleRealInfo> queryUserInfo2(String line, String date);

	@Query(value="select min(s.id), s.clZbh from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 GROUP BY s.clZbh ")
	List<ScheduleRealInfo> queryUserInfo3(String line, String date);
	
	@Query(value="select s from ScheduleRealInfo s where s.jName = ?1 and s.clZbh = ?2 and s.lpName = ?3 order by bcs")
	List<ScheduleRealInfo> exportWaybill(String jName, String clZbh, String lpName);
	
	//把sum(addMileage) 替换为0   数据表去掉了 add_mileage 字段
	@Query(value="select new map(clZbh as clZbh,jGh as jGh,jName as jName,sum(jhlc) as zgl,"
			+ "0 as ksgl,count(jName) as bcs) from ScheduleRealInfo s where"
			+ " s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 group by clZbh,jGh,jName")
	List<Map<String, Object>> dailyInfo(String line, String date);
	
	@Query(value="select t.inside_code,d.sender,d.txt_content,d.timestamp,0 as xlbm from  ("
			+ "  select equipment_code,inside_code  from bsth_c_cars where id in("
			+ " select cl from bsth_c_s_ccinfo where  xl in ( "
			+ " select id from bsth_c_line where line_code=?1 ))) t"
			+ " left join bsth_v_directive_60 d on t.equipment_code=d.device_id "
			+ " where d.timestamp >=?2 and  d.timestamp <=?3 and t.inside_code like %?4% ",nativeQuery=true)
	List<Object[]> historyMessage(String line, long d, long t, String code);
	
	@Query(value="SELECT r.xl_name,r.lp_name,r.cl_zbh,count(*) as cs "
			+ " FROM bsth_c_s_sp_info_real r RIGHT JOIN bsth_v_directive_60 d "
			+ " ON r.id = d.sch WHERE d.is_dispatch = 1 AND r.xl_bm like %?1% AND "
			+ " DATE_FORMAT(r.schedule_date,'%Y-%m-%d') = ?2 and r.cl_zbh like %?3% group by "
			+ " lp_name,xl_name,cl_zbh",nativeQuery=true)
	List<Object[]> historyMessageCount(String line, String date, String code);
	
	@Query(value = "select max(id) from ScheduleRealInfo")
	Long getMaxId();
	
	@Query(value = "select count(*) from ScheduleRealInfo s where s.scheduleDateStr = ?1")
	int countByDate(String date);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value = "select DISTINCT s from ScheduleRealInfo s where s.scheduleDateStr = ?1")
	List<ScheduleRealInfo> findByDate(String dateStr);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 and cl_zbh=?3 order by bcs")
	List<ScheduleRealInfo> findByDate2(String line, String date, String clzbh);
	
	@Query(value="select count(jName) from ScheduleRealInfo s where s.jName = ?1 and s.clZbh = ?2 and s.lpName = ?3 and s.status = -1")
	int findCjbc(String jName, String clZbh, String lpName);
	
	@Query(value="select count(jName) from ScheduleRealInfo s where s.jName = ?1 and s.clZbh = ?2 and s.lpName = ?3 and sflj != 0")
	int findLjbc(String jName, String clZbh, String lpName);
	
	@Query(value="SELECT c.company,r.request_code,FROM_UNIXTIME(r.timestamp/1000,'%Y-%m-%d %T'),c.inside_code FROM bsth_v_report_80 r LEFT JOIN bsth_c_cars c ON c.equipment_code = r.device_id where FROM_UNIXTIME(r.timestamp/1000,'%Y-%m-%d') = ?2 and r.line_id = ?1 and c.inside_code like %?3%",nativeQuery=true)
	List<Object[]> account(String line, String date, String code);
	
	@Query(value="select s from ScheduleRealInfo s where s.xlBm = ?1 and s.scheduleDate >= str_to_date(?2,'%Y-%m-%d') "
			+ " and s.scheduleDate <= str_to_date(?3,'%Y-%m-%d') and s.lpName like  %?4% "
			+ " and clZbh like %?5% order by s.fcsj")
	List<ScheduleRealInfo> correctForm(String line, String startDate, String endDate, String lpName, String code);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.jName = ?1 and s.clZbh = ?2 and s.lpName = ?3 and s.scheduleDate = str_to_date(?4,'%Y-%m-%d') and s.xlBm=?5 order by realExecDate,dfsj")
	List<ScheduleRealInfo> queryListWaybill(String jName, String clZbh, String lpName, String date, String line);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.jGh = ?1 and s.clZbh = ?2 and s.lpName = ?3 and s.scheduleDate = str_to_date(?4,'%Y-%m-%d') and s.xlBm=?5  order by realExecDate,dfsj")
	List<ScheduleRealInfo> queryListWaybillXcld(String jGh, String clZbh, String lpName, String date, String line);
	
//	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
//	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.jGh like %?1% and s.clZbh like %?2% and s.scheduleDate = str_to_date(?3,'%Y-%m-%d') and s.gsBm like %?4% and s.fgsBm like %?5%  order by realExecDate,fcsj")
//	List<ScheduleRealInfo> queryListWaybill3(String jName,String clZbh,String date,String gsbm,String fgsbm);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2  order by s.xlBm,s.clZbh,s.jGh,s.adjustExps,s.fcsj")
	List<ScheduleRealInfo> scheduleByDateAndLineTjrb(String line, String date);
	

	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where  s.clZbh = ?1  and s.scheduleDate = str_to_date(?2,'%Y-%m-%d') and xlBm =?3 order by realExecDate,dfsj")
	List<ScheduleRealInfo> queryListWaybill2(String clZbh, String date, String line);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.jGh like %?1% and s.clZbh like %?2% and s.scheduleDate = str_to_date(?3,'%Y-%m-%d') and s.gsBm like %?4% and s.fgsBm like %?5%  order by realExecDate,dfsj")
	List<ScheduleRealInfo> queryListWaybill3(String jName, String clZbh, String date, String gsbm, String fgsbm);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.jName = ?1 and s.clZbh = ?2  and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') between str_to_date(?3,'%Y-%m-%d') and str_to_date(?4,'%Y-%m-%d')  order by bcs")
	List<ScheduleRealInfo> queryListWaybill4(String jName, String clZbh, String date, String enddate);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2")
	List<ScheduleRealInfo> scheduleDaily(String line, String date);

	@Query(value = "select count(*) from ScheduleRealInfo s where s.xlBm=?1 and s.scheduleDateStr=?2")
	int countByLineCodeAndDate(String xlBm, String schDate);

	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value = "select DISTINCT s from ScheduleRealInfo s where s.xlBm=?1 and s.scheduleDateStr=?2")
	List<ScheduleRealInfo> findByLineCodeAndDate(String xlBm, String schDate);

	@Modifying
	@Transactional
	@Query(value = "delete ScheduleRealInfo s where s.xlBm=?1 and s.scheduleDateStr=?2")
	void deleteByLineCodeAndDate(String xlBm, String schDate);
	
	//去掉了 xlBm is not null 
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm like %?1%  and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 order by s.xlDir,s.realExecDate,s.dfsj, (s.lpName+1)")
	List<ScheduleRealInfo> scheduleByDateAndLine(String line, String date);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm =?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 order by (s.lpName+1), s.realExecDate,s.dfsj")
	List<ScheduleRealInfo> scheduleByDateAndLineQp(String line, String date);
	
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1 and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 and s.bcType not in ('in','out') order by s.xlBm,s.adjustExps,s.fcsj")
	List<ScheduleRealInfo> scheduleByDateAndLine2(String line, String date);

	//按月统计
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm like %?1%  and DATE_FORMAT(s.scheduleDate,'%Y-%m') = ?2 and s.bcType not in ('in','out') order by s.xlBm")
	List<ScheduleRealInfo> scheduleByDateAndLine3(String line, String date);
	
	//按照时间段统计
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm like %?1%  and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') between ?2  and ?3  and gsBm like %?4% and fgsBm like %?5% order by s.xlBm")
	List<ScheduleRealInfo> scheduleByDateAndLineTj(String line, String date, String date2, String gsdm, String fgsdm);
	
	//按照时间段统计
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1  and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') between ?2  and ?3 order by s.xlBm")
	List<ScheduleRealInfo> scheduleByDateAndLineTj2(String line, String date, String date2);
		//月报表
	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value="select DISTINCT s from ScheduleRealInfo s where s.xlBm = ?1  and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') between ?2  and ?3  order by s.xlBm")
	List<ScheduleRealInfo> scheduleByDateAndLineYbb(String line, String date, String date2);
	
	
	@Query(value="select new map(s.scheduleDate as scheduleDate,s.xlBm as xlBm,s.clZbh as clZbh,s.jGh as jGh,min(s.fcsj) as fcsj ) from ScheduleRealInfo s where s.xlBm like %?1% and DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?2 and s.gsBm like %?3% and s.fgsBm like %?4% and s.clZbh like %?5% GROUP BY xlBm,clZbh,jGh,scheduleDate,jGh ORDER BY clZbh,fcsj")
	List<Map<String,Object>> yesterdayDataList(String line, String date, String gsbm, String fgsbm, String nbbm);
	
	@Query(value="select s from ScheduleRealInfo s where DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?1 ORDER BY xlBm,lpName,clZbh,xlDir")
	List<ScheduleRealInfo> setLD(String date);
	
	@Query(value="select new map(xlBm as xlBm,lpName as lpName,clZbh as clZbh) from ScheduleRealInfo s where DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?1 GROUP BY xlBm,lpName,clZbh ORDER BY xlBm,lpName,clZbh")
	List<Map<String,Object>> setLDGroup(String date);
	
	@Query(value="select new map(xlBm as xlBm,clZbh as clZbh) from ScheduleRealInfo s where DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?1 GROUP BY xlBm,clZbh ORDER BY xlBm,clZbh")
	List<Map<String,Object>> setLCYHGroup(String date);
	
	@Query(value="select new map(xlBm as xlBm) from ScheduleRealInfo s where DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?1 GROUP BY xlBm ORDER BY xlBm")
	List<Map<String,Object>> setDDRBGroup(String date);

	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	Page<ScheduleRealInfo> findAll(Specification<ScheduleRealInfo> spec, Pageable pageable);

	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	List<ScheduleRealInfo> findAll(Specification<ScheduleRealInfo> spec);

	@EntityGraph(value = "scheduleRealInfo_cTasks", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	List<ScheduleRealInfo> findAll();

}
